﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace _1stVATCWin
{
    public partial class ThongKe : Form
    {
        public ThongKe()
        {
            InitializeComponent();
        }

        private void ThongKe_Load(object sender, EventArgs e)
        {
            loadcombobox1();
            loadcombobox2();
            loadcombobox3();
            loadcombobox4();
            loadtonkho();

        }

        private void loadcombobox1()
        {
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from MONTH ";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table MONTH");
            DataTable dt = ds.Tables["Table MONTH"];

            comboBox1.DisplayMember = "Thang";
            comboBox1.ValueMember = "Thang";
            comboBox1.DataSource = ds.Tables["Table MONTH"].DefaultView;

        }
        private void loadcombobox2()
        {
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from YEAR ";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table YEAR");
            DataTable dt = ds.Tables["Table YEAR"];

            comboBox2.DisplayMember = "NAM";
            comboBox2.ValueMember = "NAM";
            comboBox2.DataSource = ds.Tables["Table YEAR"].DefaultView;

        }
        private void loadcombobox3()
        {
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from NHACUNGCAP ";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table NHACUNGCAP");
            DataTable dt = ds.Tables["Table NHACUNGCAP"];

            comboBox3.DisplayMember = "tenNCC";
            comboBox3.ValueMember = "IDNCC";
            comboBox3.DataSource = ds.Tables["Table NHACUNGCAP"].DefaultView;
        }
        private void loadcombobox4()
        {
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from SANPHAM ";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table SANPHAM");
            DataTable dt = ds.Tables["Table SANPHAM"];

            comboBox4.DisplayMember = "tensanpham";
            comboBox4.ValueMember = "IDsanpham";
            comboBox4.DataSource = ds.Tables["Table SANPHAM"].DefaultView;
        }

        private void checkBox1_Click(object sender, EventArgs e)
        {
            if (checkBox1.Checked == true)
            {
                checkBox2.Checked = false;
                comboBox1.Enabled = true;
            }
        }
        private void checkBox2_Click(object sender, EventArgs e)
        {
            if (checkBox2.Checked == true)
            {
                checkBox1.Checked = false;
                comboBox1.Enabled = false;
                comboBox1.SelectedIndex = -1;
            }
            else
            {

                comboBox1.Enabled = true;

            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (checkBox3.Checked == false)
            {          
                loadnhap();
                loadxuat();
            }
            else
            {
                loadnhapncc();
                loadxuatNCC();
            }

        }

        private void loadnhap()
        {
            if (checkBox1.Checked == true)
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select * from LOAISANPHAM join SANPHAM on LOAISANPHAM.IDloai = SANPHAM.IDloai	join HOPDONG_CT on SANPHAM.IDsanpham = HOPDONG_CT.IDsanpham where month(ngayhopdong) =@Thang and year(ngayhopdong)=@NAM";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                adapter.Fill(ds, "Table LOAISANPHAM");
                DataTable dt = ds.Tables["Table LOAISANPHAM"];

                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = ds.Tables["Table LOAISANPHAM"].DefaultView;
                if (dataGridView1.Rows.Count == 0)
                {
                    label13.Enabled = true;
                    label13.Text = "Không có nhập hàng vào thời gian này";
                }
                else
                {
                    label13.Text = "";
                }

            }
            else
            {
               
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select * from LOAISANPHAM join SANPHAM on LOAISANPHAM.IDloai = SANPHAM.IDloai	join HOPDONG_CT on SANPHAM.IDsanpham = HOPDONG_CT.IDsanpham where  year(ngayhopdong)=@NAM";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                adapter.Fill(ds, "Table LOAISANPHAM");
                DataTable dt = ds.Tables["Table LOAISANPHAM"];

                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = ds.Tables["Table LOAISANPHAM"].DefaultView;
                if (dataGridView1.Rows.Count == 0)
                {
                    label13.Text = "Không có nhập hàng vào thời gian này";
                }
                else
                {
                    label13.Text = "";
                }

            }
        }

        private void loadxuat()
        {
            if (checkBox1.Checked == true)
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select * from LOAISANPHAM join SANPHAM on LOAISANPHAM.IDloai = SANPHAM.IDloai join PHIEUXUAT_CT on SANPHAM.IDsanpham = PHIEUXUAT_CT.IDsanpham where month(ngayxuat)=@Thang and year(ngayxuat)=@NAM";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                adapter.Fill(ds, "Table LOAISANPHAM");
                DataTable dt = ds.Tables["Table LOAISANPHAM"];

                dataGridView2.AutoGenerateColumns = false;
                dataGridView2.DataSource = ds.Tables["Table LOAISANPHAM"].DefaultView;
                if (dataGridView2.Rows.Count == 0)
                {
                    label14.Text = "Không có xuất hàng vào thời gian này";
                }
                else
                {
                    label14.Text = "";

                }

            }
            else
            {
           
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select * from LOAISANPHAM join SANPHAM on LOAISANPHAM.IDloai = SANPHAM.IDloai join PHIEUXUAT_CT on SANPHAM.IDsanpham = PHIEUXUAT_CT.IDsanpham where year(ngayxuat)=@NAM";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                adapter.Fill(ds, "Table LOAISANPHAM");
                DataTable dt = ds.Tables["Table LOAISANPHAM"];

                dataGridView2.AutoGenerateColumns = false;
                dataGridView2.DataSource = ds.Tables["Table LOAISANPHAM"].DefaultView;
                if (dataGridView2.Rows.Count == 0)
                {
                    label14.Text = "Không có xuất hàng vào thời gian này";
                }
                else
                {
                    label14.Text = "";
                }

            }
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            autoloadnhap();
            tinh1();
        }
        // click load nhap
        private void autoloadnhap()
        {
            int i = dataGridView1.SelectedRows[0].Index;
            string IDsanpham = dataGridView1.Rows[i].Cells["IDsanpham"].Value.ToString();
            string IDhopdong = dataGridView1.Rows[i].Cells["IDhopdong"].Value.ToString();
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            // string sql = "select * from HOPDONG_CT join TONKHO on HOPDONG_CT.IDsanpham = TONKHO.IDsanpham where IDsanpham=@IDsanpham  ";
            string sql = "select * from HOPDONG_CT where IDsanpham=@IDsanpham and IDhopdong=@IDhopdong";
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@IDsanpham", IDsanpham);
            cmd.Parameters.AddWithValue("@IDhopdong", IDhopdong);
            conn.Open();
            SqlDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                textBox1.Text = r["slnhap"].ToString();
                textBox2.Text = r["dongianhap"].ToString();

            }
            r.Close();
            conn.Close();
        }

        private void dataGridView2_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            autoloadxuat();
            tinh2();
        }
        // click load xuat
        private void autoloadxuat()
        {
            int i = dataGridView2.SelectedRows[0].Index;
            string IDsanpham = dataGridView2.Rows[i].Cells["ID"].Value.ToString();
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            // string sql = "select * from HOPDONG_CT join TONKHO on HOPDONG_CT.IDsanpham = TONKHO.IDsanpham where IDsanpham=@IDsanpham  ";
            string sql = "select * from PHIEUXUAT_CT where IDsanpham=@ID ";
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@ID", IDsanpham);
            conn.Open();
            SqlDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                textBox3.Text = r["slxuat"].ToString();
                textBox4.Text = r["dongiaxuat"].ToString();

            }
            r.Close();
            conn.Close();
        }
        private void tinh1()
        {
            int sl = int.Parse(textBox1.Text);
            int gia = int.Parse(textBox2.Text);
            int tienmua = (sl * gia);
            textBox5.Text = tienmua.ToString();
        }

        private void tinh2()
        {
            int sl = int.Parse(textBox3.Text);
            int gia = int.Parse(textBox4.Text);
            int tienban = (sl * gia);
            textBox6.Text = tienban.ToString();
        }
        // tính tiền nhập
        private void button2_Click(object sender, EventArgs e)
        {
            if (checkBox3.Checked == true)
                tinhtiennhap();
            else
                tinhtiennhap1();
        }

        private void tinhtiennhap()
        {
            if (checkBox1.Checked == true)
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select sum(slnhap * dongianhap) as tongnhap from HOPDONG_CT join HOPDONG on HOPDONG_CT.IDhopdong = HOPDONG.IDhopdong  join NHACUNGCAP on HOPDONG.IDNCC = NHACUNGCAP.IDNCC where year(HOPDONG.ngayhopdong)=@NAM and month (HOPDONG.ngayhopdong) =@Thang and NHACUNGCAP.IDNCC = @IDNCC and xacnhan = N'Đã Xác Nhận'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    textBox9.Text = r["tongnhap"].ToString();
                }
                r.Close();
                conn.Close();
            }
            else
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select sum(slnhap * dongianhap) as tongnhap from HOPDONG_CT join HOPDONG on HOPDONG_CT.IDhopdong = HOPDONG.IDhopdong join NHACUNGCAP on HOPDONG.IDNCC = NHACUNGCAP.IDNCC where year(HOPDONG.ngayhopdong)=@NAM and NHACUNGCAP.IDNCC = @IDNCC and xacnhan = N'Đã Xác Nhận'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                //cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    textBox9.Text = r["tongnhap"].ToString();
                }
                r.Close();
                conn.Close();
            }
        }
        private void tinhtiennhap1()
        {
            if (checkBox1.Checked == true)
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select sum(slnhap * dongianhap) as tongnhap from HOPDONG_CT join HOPDONG on HOPDONG_CT.IDhopdong = HOPDONG.IDhopdong  join NHACUNGCAP on HOPDONG.IDNCC = NHACUNGCAP.IDNCC where year(HOPDONG.ngayhopdong)=@NAM and month (HOPDONG.ngayhopdong) =@Thang  and xacnhan = N'Đã Xác Nhận'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    textBox9.Text = r["tongnhap"].ToString();
                }
                r.Close();
                conn.Close();
            }
            else
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select sum(slnhap * dongianhap) as tongnhap from HOPDONG_CT join HOPDONG on HOPDONG_CT.IDhopdong = HOPDONG.IDhopdong join NHACUNGCAP on HOPDONG.IDNCC = NHACUNGCAP.IDNCC where year(HOPDONG.ngayhopdong)=@NAM and xacnhan = N'Đã Xác Nhận'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                //cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
               

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    textBox9.Text = r["tongnhap"].ToString();
                }
                r.Close();
                conn.Close();
            }
        }
        // tính tiền bán
        private void button4_Click(object sender, EventArgs e)
        {
            if (checkBox3.Checked == true)
                tinhtienban();
            else
                tinhtienban1();
        }

        private void tinhtienban()
        {
            if (checkBox1.Checked == true)
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select sum((slxuat * dongiaxuat)- (((slxuat*dongiaxuat)*10)/100)) as tongxuat from NHACUNGCAP join SANPHAM_CT on NHACUNGCAP.IDNCC = SANPHAM_CT.IDNCC join SANPHAM on SANPHAM_CT.IDsanpham = SANPHAM.IDsanpham  join PHIEUXUAT_CT on SANPHAM.IDsanpham = PHIEUXUAT_CT.IDsanpham  join PHIEUXUAT on PHIEUXUAT.IDphieuxuat= PHIEUXUAT_CT.IDphieuxuat where month(PHIEUXUAT.ngayxuat)=@Thang and year(PHIEUXUAT.ngayxuat)=@NAM and NHACUNGCAP.IDNCC = @IDNCC ";

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    textBox10.Text = r["tongxuat"].ToString();
                }
                r.Close();
                conn.Close();
            }
            else
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select sum((slxuat * dongiaxuat)- (((slxuat*dongiaxuat)*10)/100)) as tongxuat from NHACUNGCAP join SANPHAM_CT on NHACUNGCAP.IDNCC = SANPHAM_CT.IDNCC join SANPHAM on SANPHAM_CT.IDsanpham = SANPHAM.IDsanpham  join PHIEUXUAT_CT on SANPHAM.IDsanpham = PHIEUXUAT_CT.IDsanpham  join PHIEUXUAT on PHIEUXUAT.IDphieuxuat= PHIEUXUAT_CT.IDphieuxuat where year(PHIEUXUAT.ngayxuat)=@NAM and NHACUNGCAP.IDNCC = @IDNCC ";

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    textBox10.Text = r["tongxuat"].ToString();
                }
                r.Close();
                conn.Close();
            }
        }

        private void tinhtienban1()
        {
            if (checkBox1.Checked == true)
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select sum((slxuat * dongiaxuat) - (((slxuat*dongiaxuat)*10)/100)) as tongxuat from NHACUNGCAP join SANPHAM_CT on NHACUNGCAP.IDNCC = SANPHAM_CT.IDNCC join SANPHAM on SANPHAM_CT.IDsanpham = SANPHAM.IDsanpham  join PHIEUXUAT_CT on SANPHAM.IDsanpham = PHIEUXUAT_CT.IDsanpham  join PHIEUXUAT on PHIEUXUAT.IDphieuxuat= PHIEUXUAT_CT.IDphieuxuat where month(PHIEUXUAT.ngayxuat)=@Thang and year(PHIEUXUAT.ngayxuat)=@NAM ";

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    textBox10.Text = r["tongxuat"].ToString() ;
                }
                r.Close();
                conn.Close();
            }
            else
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select sum((slxuat * dongiaxuat) - (((slxuat*dongiaxuat)*10)/100)) as tongxuat from NHACUNGCAP join SANPHAM_CT on NHACUNGCAP.IDNCC = SANPHAM_CT.IDNCC join SANPHAM on SANPHAM_CT.IDsanpham = SANPHAM.IDsanpham  join PHIEUXUAT_CT on SANPHAM.IDsanpham = PHIEUXUAT_CT.IDsanpham  join PHIEUXUAT on PHIEUXUAT.IDphieuxuat= PHIEUXUAT_CT.IDphieuxuat where year(PHIEUXUAT.ngayxuat)=@NAM ";

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    textBox10.Text = r["tongxuat"].ToString();
                }
                r.Close();
                conn.Close();
            }
        }

        private void checkBox3_Click(object sender, EventArgs e)
        {
            if (checkBox3.Checked == true)
            {
                comboBox3.Enabled = true;
            }
            else
            {
                comboBox3.Enabled = false;
            }
        }

        // load datagridview nhap
        private void loadnhapncc()
        {
            if (checkBox1.Checked == true)
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select * from LOAISANPHAM join SANPHAM on LOAISANPHAM.IDloai = SANPHAM.IDloai	join HOPDONG_CT on SANPHAM.IDsanpham = HOPDONG_CT.IDsanpham join HOPDONG on HOPDONG_CT.IDhopdong =HOPDONG.IDhopdong join NHACUNGCAP on NHACUNGCAP.IDNCC=HOPDONG.IDNCC  where month (HOPDONG.ngayhopdong) =@Thang and year(HOPDONG.ngayhopdong)=@NAM and NHACUNGCAP.IDNCC = @IDNCC order by NHACUNGCAP.IDNCC";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                adapter.Fill(ds, "Table LOAISANPHAM");
                DataTable dt = ds.Tables["Table LOAISANPHAM"];

                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = ds.Tables["Table LOAISANPHAM"].DefaultView;
                if (dataGridView1.Rows.Count == 0)
                {
                    label13.Enabled = true;
                    label13.Text = "Không có nhập hàng vào thời gian này";
                }
                else
                {
                    label13.Text = "";
                }

            }
            else
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select * from LOAISANPHAM join SANPHAM on LOAISANPHAM.IDloai = SANPHAM.IDloai	join HOPDONG_CT on SANPHAM.IDsanpham = HOPDONG_CT.IDsanpham join HOPDONG on HOPDONG_CT.IDhopdong =HOPDONG.IDhopdong join NHACUNGCAP on NHACUNGCAP.IDNCC=HOPDONG.IDNCC  where  year(HOPDONG.ngayhopdong)=@NAM and NHACUNGCAP.IDNCC = @IDNCC order by NHACUNGCAP.IDNCC";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                adapter.Fill(ds, "Table LOAISANPHAM");
                DataTable dt = ds.Tables["Table LOAISANPHAM"];

                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = ds.Tables["Table LOAISANPHAM"].DefaultView;
                if (dataGridView1.Rows.Count == 0)
                {
                    label13.Enabled = true;
                    label13.Text = "Không có nhập hàng vào thời gian này";
                }
                else
                {
                    label13.Text = "";
                }

            }
        }
        // load datagridview xuat
        private void loadxuatNCC()
        {
            if (checkBox1.Checked == true)
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select * from NHACUNGCAP join SANPHAM_CT on NHACUNGCAP.IDNCC = SANPHAM_CT.IDNCC join SANPHAM on SANPHAM_CT.IDsanpham = SANPHAM.IDsanpham  join PHIEUXUAT_CT on SANPHAM.IDsanpham = PHIEUXUAT_CT.IDsanpham  join PHIEUXUAT on PHIEUXUAT.IDphieuxuat= PHIEUXUAT_CT.IDphieuxuat  where  month(PHIEUXUAT.ngayxuat)=@Thang and year(PHIEUXUAT.ngayxuat)=@NAM and  NHACUNGCAP.IDNCC = @IDNCC order by NHACUNGCAP.IDNCC";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                adapter.Fill(ds, "Table LOAISANPHAM");
                DataTable dt = ds.Tables["Table LOAISANPHAM"];

                dataGridView2.AutoGenerateColumns = false;
                dataGridView2.DataSource = ds.Tables["Table LOAISANPHAM"].DefaultView;
                if (dataGridView2.Rows.Count == 0)
                {
                    label14.Enabled = true;
                    label14.Text = "Không có xuất hàng vào thời gian này";
                }
                else
                {
                    label14.Text = "";
                }
            }
            else
            {
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select * from NHACUNGCAP join SANPHAM_CT on NHACUNGCAP.IDNCC = SANPHAM_CT.IDNCC join SANPHAM on SANPHAM_CT.IDsanpham = SANPHAM.IDsanpham  join PHIEUXUAT_CT on SANPHAM.IDsanpham = PHIEUXUAT_CT.IDsanpham  join PHIEUXUAT on PHIEUXUAT.IDphieuxuat= PHIEUXUAT_CT.IDphieuxuat  where year(PHIEUXUAT.ngayxuat)=@NAM and  NHACUNGCAP.IDNCC = @IDNCC order by NHACUNGCAP.IDNCC";
                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
                cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                adapter.Fill(ds, "Table LOAISANPHAM");
                DataTable dt = ds.Tables["Table LOAISANPHAM"];

                dataGridView2.AutoGenerateColumns = false;
                dataGridView2.DataSource = ds.Tables["Table LOAISANPHAM"].DefaultView;
                if (dataGridView2.Rows.Count == 0)
                {
                    label14.Enabled = true;
                    label14.Text = "Không có xuất hàng vào thời gian này";
                }
                else
                {
                    label14.Text = "";
                }
            }
        }

        // load tồn kho
        private void loadtonkho()
        {
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from SANPHAM where IDsanpham = @IDsanpham";

            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@IDsanpham", comboBox4.SelectedValue);
            conn.Open();
            SqlDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                textBox7.Text = r["soluongton"].ToString();
            }
            r.Close();
            conn.Close();
        }

        private void comboBox4_SelectedIndexChanged(object sender, EventArgs e)
        {
            loadtonkho();
        }
        // tính tổng lãi
        private void button1_Click(object sender, EventArgs e)
        {
            //if (checkBox1.Checked == true)
            //{
            //    Config file = new Config(Application.StartupPath + "\\config.ini");
            //    string connectionString = file.ReadValue("server", "conn");
            //    SqlConnection conn = new SqlConnection(connectionString);
            //    string sql = "select sum((slxuat * dongiaxuat)-(slnhap * dongianhap)) as tonglai from LOAISANPHAM join SANPHAM on LOAISANPHAM.IDloai = SANPHAM.IDloai	join HOPDONG_CT on SANPHAM.IDsanpham = HOPDONG_CT.IDsanpham join HOPDONG on HOPDONG_CT.IDhopdong =HOPDONG.IDhopdong join NHACUNGCAP on NHACUNGCAP.IDNCC=HOPDONG.IDNCC join PHIEUXUAT_CT on HOPDONG_CT.IDsanpham = PHIEUXUAT_CT.IDsanpham join PHIEUXUAT on PHIEUXUAT.IDphieuxuat= PHIEUXUAT_CT.IDphieuxuat where month (PHIEUXUAT.ngayxuat) =@Thang and year(PHIEUXUAT.ngayxuat)=@NAM and NHACUNGCAP.IDNCC = @IDNCC ";

            //    SqlCommand cmd = new SqlCommand(sql, conn);
            //    cmd.Parameters.AddWithValue("@Thang", comboBox1.SelectedValue);
            //    cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
            //    cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);

            //    conn.Open();
            //    SqlDataReader r = cmd.ExecuteReader();
            //    while (r.Read())
            //    {
            //        textBox11.Text = r["tonglai"].ToString();
            //    }
            //    r.Close();
            //    conn.Close();
            //}
            //else
            //{
            //Config file = new Config(Application.StartupPath + "\\config.ini");
            //string connectionString = file.ReadValue("server", "conn");
            //SqlConnection conn = new SqlConnection(connectionString);
            //string sql = "select sum((slxuat * dongiaxuat) - tonggianhap ) as tonglai from LOAISANPHAM join SANPHAM on LOAISANPHAM.IDloai = SANPHAM.IDloai	join HOPDONG_CT on SANPHAM.IDsanpham = HOPDONG_CT.IDsanpham join HOPDONG on HOPDONG_CT.IDhopdong =HOPDONG.IDhopdong join NHACUNGCAP on NHACUNGCAP.IDNCC=HOPDONG.IDNCC join PHIEUXUAT_CT on HOPDONG_CT.IDsanpham = PHIEUXUAT_CT.IDsanpham join PHIEUXUAT on PHIEUXUAT.IDphieuxuat= PHIEUXUAT_CT.IDphieuxuat where year(PHIEUXUAT.ngayxuat)=@NAM and NHACUNGCAP.IDNCC = @IDNCC ";

            //SqlCommand cmd = new SqlCommand(sql, conn);

            //cmd.Parameters.AddWithValue("@NAM", comboBox2.SelectedValue);
            //cmd.Parameters.AddWithValue("@IDNCC", comboBox3.SelectedValue);

            //conn.Open();
            //SqlDataReader r = cmd.ExecuteReader();
            //while (r.Read())
            //{
            //    textBox11.Text = r["tonglai"].ToString();
            //}
            //r.Close();
            //conn.Close();
            if (checkBox3.Checked == true)
            {
                tinhtiennhap();
                tinhtienban();
              
                if (textBox10.Text == "" && textBox9.Text == "")
                {
                    textBox11.Text = "0";
                }
                else
                {
                    if (textBox9.Text == "")
                    {

                        double tienban = int.Parse(textBox10.Text);                      
                        textBox11.Text = tienban.ToString();
                    }
                    else
                    {
                        if (textBox10.Text == "")
                        {
                            int tiennhap = int.Parse(textBox9.Text);
                            int b = 0 - tiennhap;
                            textBox11.Text = b.ToString();
                        }

                        else
                        {
                            int tienban = int.Parse(textBox10.Text);
                            int tiennhap = int.Parse(textBox9.Text);
                            double tongtien = tienban -  tiennhap;
                            textBox11.Text = tongtien.ToString();
                        }
                    }
                }


            }
            else
            {
               
                tinhtiennhap1();
                tinhtienban1();
                if (textBox10.Text == "" && textBox9.Text == "")
                {
                    textBox11.Text = "0";
                }
                else
                {
                    if (textBox9.Text == "")
                    {

                        double tienban = int.Parse(textBox10.Text) ;
                        textBox11.Text = tienban.ToString();
                    }
                    else
                    {
                        if (textBox10.Text == "")
                        {
                            int tiennhap = int.Parse(textBox9.Text);
                            int b = 0 - tiennhap;
                            textBox11.Text = b.ToString();
                        }

                        else
                        {
                            int tienban = int.Parse(textBox10.Text);
                            int tiennhap = int.Parse(textBox9.Text);
                            double tongtien = tienban - tiennhap;
                            textBox11.Text = tongtien.ToString();
                        }
                    }
                }
            }
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox1.Checked)
                loadcombobox1();
        }

     }

}



